package com.lehuo.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.lehuo.bean.Bbs;
import com.lehuo.bean.Bbs_com;
import com.lehuo.bean.Bbs_img;

public class BbsUtil {
	private static Statement statement = null;

	public static String addBbs(String user_id, String add_time,
			String bbs_title, String bbs_content, String bbs_type,
			Connection connection) {

		String getUser_sql = "select * from user where id= '" + user_id + "'";
		JSONObject jsonObject = new JSONObject();
		connection = ConnUtil.getConnection();
		try {
			statement = (Statement) connection.createStatement();

			ResultSet rs_user = null;
			System.out.println("sql:" + getUser_sql);
			rs_user = statement.executeQuery(getUser_sql);
			String user_nick = null;
			String user_thum = null;
			if (rs_user.next()) {
				user_nick = rs_user.getString("nickname");
				user_thum = rs_user.getString("thumbnail");
			} else {
				jsonObject.put("result", "error");
				return jsonObject.toString();
			}

			String addBbs_sql = "insert into bbs(user_id,user_nick,"
					+ "user_thum,add_time,bbs_title,"
					+ "bbs_content,zan_num,review_num,bbs_type)values('"
					+ user_id + "','" + user_nick + "','" + user_thum + "','"
					+ add_time + "','" + bbs_title + "','" + bbs_content
					+ "','" + "0" + "','" + "0" + "','" + bbs_type + "');";
			System.out.println("addRepair_sql:" + addBbs_sql);
			int count = statement.executeUpdate(addBbs_sql,
					Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = statement.getGeneratedKeys();
			int key = -1;
			if (rs.next()) {
				key = rs.getInt(1);
				System.out.println(":" + key);
			}
			if (count > 0) {
				jsonObject.put("result", "success");
				jsonObject.put("id", key);
			} else {
				jsonObject.put("result", "error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonObject.toString();
	}

	public static String getBbs(String user_id, Connection connection) {
		ResultSet rs = null;
		JSONArray jsonArray = null;
		String sql = "select * from bbs where user_id= '" + user_id
				+ "' order by id desc";
		List<Bbs> bbList = new ArrayList<Bbs>();
		connection = ConnUtil.getConnection();
		try {
			connection = ConnUtil.getConnection();
			statement = (Statement) connection.createStatement();
			System.out.println("sql:" + sql);
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				String user_sql = "select * from user where id= '" + user_id
						+ "'";
				Statement statement2 = (Statement) connection.createStatement();
				ResultSet rs2 = statement2.executeQuery(user_sql);
				if (rs2.next()) {

					List<Bbs_img> bbs_imgs = new ArrayList<Bbs_img>();
					String bbs_id = rs.getInt("id") + ""; // 获取bbs_id
					String bbs_img_sql = "select * from bbs_img where bbs_id= '"
							+ bbs_id + "'";
					System.out.println("bbs_img_sql:" + bbs_img_sql);
					Statement statement3 = (Statement) connection
							.createStatement();
					ResultSet rs3 = statement3.executeQuery(bbs_img_sql);
					while (rs3.next()) { // 获取bbs_img
						Bbs_img bbs_img = new Bbs_img(rs3.getInt("id") + "",
								rs3.getString("bbs_id"),
								rs3.getString("bbs_img"));
						bbs_imgs.add(bbs_img);
					}

					Bbs bbs = new Bbs(rs.getInt("id") + "",
							rs2.getString("id"), rs2.getString("nickname"),
							rs2.getString("thumbnail"),
							rs.getString("add_time"),
							rs.getString("bbs_title"),
							rs.getString("bbs_content"),
							rs.getString("zan_num"),
							rs.getString("review_num"),
							rs.getString("bbs_type"), bbs_imgs);
					bbList.add(bbs);
				}
			}
			jsonArray = JSONArray.fromObject(bbList);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonArray.toString();
	}

	public static String queryBbs(Connection connection) {
		ResultSet rs = null;
		JSONArray jsonArray = null;
		String sql = "select * from bbs order by id desc";
		List<Bbs> bbList = new ArrayList<Bbs>();
		try {
			connection = ConnUtil.getConnection();
			statement = (Statement) connection.createStatement();
			System.out.println("sql:" + sql);
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				String user_id = rs.getString("user_id");
				String user_sql = "select * from user where id= '" + user_id
						+ "'";
				Statement statement2 = (Statement) connection.createStatement();
				ResultSet rs2 = statement2.executeQuery(user_sql);
				if (rs2.next()) {

					List<Bbs_img> bbs_imgs = new ArrayList<Bbs_img>();
					String bbs_id = rs.getInt("id") + ""; // 获取bbs_id
					String bbs_img_sql = "select * from bbs_img where bbs_id= '"
							+ bbs_id + "'";
					System.out.println("bbs_img_sql:" + bbs_img_sql);
					Statement statement3 = (Statement) connection
							.createStatement();
					ResultSet rs3 = statement3.executeQuery(bbs_img_sql);
					while (rs3.next()) { // 获取bbs_img
						Bbs_img bbs_img = new Bbs_img(rs3.getInt("id") + "",
								rs3.getString("bbs_id"),
								rs3.getString("bbs_img"));
						bbs_imgs.add(bbs_img);
					}

					Bbs bbs = new Bbs(bbs_id, rs2.getString("id"),
							rs2.getString("nickname"),
							rs2.getString("thumbnail"),
							rs.getString("add_time"),
							rs.getString("bbs_title"),
							rs.getString("bbs_content"),
							rs.getString("zan_num"),
							rs.getString("review_num"),
							rs.getString("bbs_type"), bbs_imgs);
					bbList.add(bbs);

				}
			}
			jsonArray = JSONArray.fromObject(bbList);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonArray.toString();
	}

	public static String deleteBbs(String id, Connection connection) {
		PreparedStatement pstmt = null;
		JSONObject jsonObject = new JSONObject();
		String deleteBbs_sql = "delete from bbs where id=" + id;
		try {
			connection = ConnUtil.getConnection();
			System.out.println("deleteBbs_sql:" + deleteBbs_sql);
			pstmt = connection.prepareStatement(deleteBbs_sql);
			int num = pstmt.executeUpdate();
			if (num > 0) {
				jsonObject.put("result", "success");
			} else {
				jsonObject.put("result", "error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonObject.toString();
	}

	public static String addBbsImg(String bbs_id, String bbs_img,
			Connection connection) {
		String addBbsImg_sql = "insert into bbs_img(bbs_id,bbs_img)values('"
				+ bbs_id + "','" + bbs_img + "');";
		JSONObject jsonObject = new JSONObject();
		try {
			connection = ConnUtil.getConnection();
			statement = (Statement) connection.createStatement();
			System.out.println("addBbsImg_sql:" + addBbsImg_sql);
			int count = statement.executeUpdate(addBbsImg_sql,
					Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = statement.getGeneratedKeys();
			int key = -1;
			if (rs.next()) {
				key = rs.getInt(1);
				System.out.println(":" + key);
			}
			if (count > 0) {
				jsonObject.put("result", "success");
				jsonObject.put("id", key);
			} else {
				jsonObject.put("result", "error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonObject.toString();
	}

	public static String addBbscom(String bbs_id, String user_id,
			String com_des, Connection connection) {
		JSONObject jsonObject = new JSONObject();
		String addBbscom_sql = "insert into bbs_com(bbs_id,user_id,com_des,time)values('"
				+ bbs_id
				+ "','"
				+ user_id
				+ "','"
				+ com_des
				+ "','"
				+ StringUtil.getTime() + "');";
		try {
			connection = ConnUtil.getConnection();
			statement = (Statement) connection.createStatement();
			System.out.println("addBbscom_sql:" + addBbscom_sql);
			int count = statement.executeUpdate(addBbscom_sql,
					Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = statement.getGeneratedKeys();
			int key = -1;
			if (rs.next()) {
				key = rs.getInt(1);
				System.out.println(":" + key);
			}
			if (count > 0) {
				jsonObject.put("result", "success");
				jsonObject.put("id", key);
			} else {
				jsonObject.put("result", "error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonObject.toString();
	}

	public static String getBbscom(String bbs_id, Connection connection) {
		ResultSet rs = null;
		JSONArray jsonArray = null;
		String sql = "select * from bbs_com where bbs_id= '" + bbs_id
				+ "' order by id asc";
		List<Bbs_com> bbs_coms = new ArrayList<Bbs_com>();
		try {
			connection = ConnUtil.getConnection();
			statement = (Statement) connection.createStatement();
			System.out.println("sql:" + sql);
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString("id");
				String user_id = rs.getString("user_id");
				String com_des = rs.getString("com_des");
				String time = rs.getString("time");
				String user_sql = "select * from user where id= '" + user_id
						+ "'";
				
				System.out.println("user_sql:" + user_sql);
				Statement statement2 = (Statement) connection.createStatement();
				ResultSet rs2 = statement2.executeQuery(user_sql);
				if (rs2.next()) {
					String user_nickname = rs2.getString("nickname");
					String user_thumbnail = rs2.getString("thumbnail");
					Bbs_com bbs_com = new Bbs_com(id, bbs_id, user_id, com_des,
							time, user_nickname, user_thumbnail);
					bbs_coms.add(bbs_com);
				}
			}
			jsonArray = JSONArray.fromObject(bbs_coms);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonArray.toString();
	}
}
